﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;
using System.Text;


[WebService(Namespace = "http://www.avila.edu/WebServices/dbConn/")] //For Production Server AND Publish this website into W:\www.avila.edu\WebServices\dbConn
//[WebService(Namespace = "http://intra.avila.edu/WebServices/dbConn/")] // For Intra Server AND Publish this website into W:\intra.avila.edu\WebServices\dbConn
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class Service : System.Web.Services.WebService
{
    // define the local key and vector byte arrays

    private byte[] key = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
              13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24};
    private byte[] iv = { 8, 7, 6, 5, 4, 3, 2, 1 };

    //[WebMethod]
    //public string getMajor()
    //{
    //    string major = ""; 
    //    var sb = new StringBuilder();
    //    SqlDataReader reader;
    //    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["prodTmsEPrd"].ConnectionString);
    //     SqlCommand cmd = new SqlCommand("SELECT pcf.PROG_CDE, pdf.prog_desc FROM program_config pcf inner join  program_def pdf on pdf.prog_cde = pcf.prog_cde   where pcf.DIV_CDE ='UG' and pdf.SHOW_ON_WEB='Y' and pcf.CANDIDACY_TYPE='F' ORDER BY pdf.prog_desc ASC ", conn);


    //    try
    //    {
    //        conn.Open();
    //        reader = cmd.ExecuteReader();
    //        while (reader.Read())
    //        {
               


    //           //code +=  "\r\n" + Convert.ToString(reader["PROG_CDE"]);

    //            string res = "";


    //            res = Convert.ToString(reader["prog_desc"]);

             

    //            sb.Append(res);
              


    //            //sb.AppendLine(); // which is equal to Append(Environment.NewLine); 
             
    //            sb.Append(Environment.NewLine);
             
    //            major = sb.ToString() + "\r\n";
               
    //        }

    //        return major;
           

    //    }
    //    finally
    //    {
    //        //reader.Close();
    //        conn.Close();

    //    }

    //    //return code;

    //}



    [WebMethod]
    public string decrypt(string input)
    {
        //define the local key and vector byte arrays

        byte[] key = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
              13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24};
        byte[] iv = { 8, 7, 6, 5, 4, 3, 2, 1 };

        cTripleDES des = new cTripleDES(key, iv);

        string res = des.Decrypt(input);
        return res;

    }

    [WebMethod]
    //Update only Name,Address and EMail
    public string InsertEduConferenceReg(string fname, string lname, string grade, string sd, string sn, string email, string phone, string add, string city, string state, string zip, string rt, string game, string creat,string mobile)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_EduConferenceReg_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@fname", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@lname", SqlDbType.VarChar).Value = lname;
              
                myCmd.Parameters.Add("@gradelevel", SqlDbType.VarChar).Value = grade;
                myCmd.Parameters.Add("@SchoolDistrict", SqlDbType.VarChar).Value = sd;
                myCmd.Parameters.Add("@SchoolName", SqlDbType.VarChar).Value = sn;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@address", SqlDbType.VarChar).Value = add;
                myCmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                myCmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state;
                myCmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zip;
                myCmd.Parameters.Add("@RealTime", SqlDbType.VarChar).Value = rt;
                myCmd.Parameters.Add("@Gaming", SqlDbType.VarChar).Value = game;
                myCmd.Parameters.Add("@Creativity", SqlDbType.VarChar).Value = creat;
                myCmd.Parameters.Add("@Mobile", SqlDbType.VarChar).Value = mobile;

                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }















    [WebMethod] 
    public string Decrypt(string p1, string p2)
    {
        //define the local key and vector byte arrays

        byte[] key = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
              13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24};
        byte[] iv = { 8, 7, 6, 5, 4, 3, 2, 1 };

        cTripleDES des = new cTripleDES(key, iv);
        string res = des.Decrypt(p1) + des.Decrypt(p2);
        return res;

    }


    [WebMethod]
    public string Encrypt(string input)
    {
        // define the local key and vector byte arrays

        byte[] key = {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 
              13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24};
        byte[] iv = { 8, 7, 6, 5, 4, 3, 2, 1 };

        cTripleDES des = new cTripleDES(key, iv);
        string res = des.Encrypt(input);
        return res;

    }
    [WebMethod]
    public string getID(string p1, string p2, string pwrd)
    {
        string id = "Not Found";
        if (pwrd == "#123xyz!")
        {
            cTripleDES des = new cTripleDES(key, iv);
            id = des.Decrypt(p1) + des.Decrypt(p2);
        }

        return id;
    }



    [WebMethod]
    //Achieve
    public string InsertFromAchieve(string fname, string lname, string email, string cemail, string phone)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_achieve_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;


                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }

    [WebMethod]
    //Catholic
    public string InsertPayment(string  payment,string lname,string email)
    {
        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();
                SqlCommand myCmd = new SqlCommand("UPDATE edu_conference_reg  SET Payment = @cancel  WHERE lname = @lname AND Email = @email" , myConn );
                myCmd.CommandType = CommandType.Text;
                myCmd.Parameters.Add("@cancel", SqlDbType.Int).Value = Convert.ToInt32(payment);
                myCmd.Parameters.Add("@lname", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.ExecuteNonQuery();
                string res = "Updated ";
                return res;
            }
            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }

    }




    [WebMethod]
    //Catholic
    public string InsertFromCatholic(string fname, string lname, string add, string city, string state, string zip, string email, string cemail, string phone,string major) //,string hs,string Gyear)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_catholic_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@address", SqlDbType.VarChar).Value = add;
                myCmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                myCmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state;
                myCmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zip;
                myCmd.Parameters.Add("@major", SqlDbType.VarChar).Value = major;
                //myCmd.Parameters.Add("@hs", SqlDbType.VarChar).Value = hs;
                //myCmd.Parameters.Add("@Gyear", SqlDbType.VarChar).Value = Gyear;

                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }

    [WebMethod]
    //Update only Name,Address and EMail
    public string InsertFromLandingPage(string fname, string lname, string add, string city, string state, string zip, string email, string cemail, string phone,string landingpage,string major)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_LandingPage_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@address", SqlDbType.VarChar).Value = add;
                myCmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                myCmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state;
                myCmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zip;
                myCmd.Parameters.Add("@landingpage", SqlDbType.VarChar).Value = landingpage;
                myCmd.Parameters.Add("@major", SqlDbType.VarChar).Value = major;
             
                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }



    [WebMethod]
    // JCCCTransfer
    public string InsertFromJCCCTransfer(string fname, string lname, string add, string city, string state, string zip, string email, string cemail, string phone, bool day, bool night,string landingpage)
    {


        using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                Conn.Open();
                SqlCommand Cmd = new SqlCommand("CUS_sp_JCCCTransfer_INSERT", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                Cmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                Cmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                Cmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                Cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = add;
                Cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                Cmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state;
                Cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zip;
                Cmd.Parameters.Add("@day", SqlDbType.VarChar).Value = day;
                Cmd.Parameters.Add("@night", SqlDbType.VarChar).Value = night;
                Cmd.Parameters.Add("@landingpage", SqlDbType.VarChar).Value = landingpage;
                Cmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                Conn.Close();
            }

        }



    }

    [WebMethod]
    // LongviewTransfer
    public string InsertFromLongviewTransfer(string fname, string lname, string add, string city, string state, string zip, string email, string cemail, string phone, bool day, bool night)
    {


        using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                Conn.Open();
                SqlCommand Cmd = new SqlCommand("CUS_sp_LongviewTransfer_INSERT", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                Cmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                Cmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                Cmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                Cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = add;
                Cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                Cmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state;
                Cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zip;
                Cmd.Parameters.Add("@day", SqlDbType.VarChar).Value = day;
                Cmd.Parameters.Add("@night", SqlDbType.VarChar).Value = night;
                Cmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                Conn.Close();
            }

        }



    }



    [WebMethod]
    ////Masters
    public string InsertFromMasters(string fname, string lname, string email, string cemail, string phone,bool mba,bool Edu,bool Psyc, bool Adv)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_masters_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@MBA", SqlDbType.VarChar).Value = mba;
                myCmd.Parameters.Add("@Education", SqlDbType.VarChar).Value = Edu;
                myCmd.Parameters.Add("@Psychology", SqlDbType.VarChar).Value = Psyc;
                myCmd.Parameters.Add("@Advantage", SqlDbType.VarChar).Value = Adv;


                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }
    }

   [WebMethod]
    ////mymba
    public string InsertFromMyMBA(string fname, string lname, string email, string cemail, string phone,   bool AC, bool FI,bool HC,bool IB,bool MAR,bool MAN,bool IS)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_mymba_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@Accounting ", SqlDbType.VarChar).Value = AC;
                myCmd.Parameters.Add("@Finance", SqlDbType.VarChar).Value = FI;
                myCmd.Parameters.Add("@HealthCare", SqlDbType.VarChar).Value = HC;
                myCmd.Parameters.Add("@InternationalBusiness", SqlDbType.VarChar).Value = IB;
                myCmd.Parameters.Add("@InformationSystem ", SqlDbType.VarChar).Value = IS;
                myCmd.Parameters.Add("@Marketing", SqlDbType.VarChar).Value = MAR;
                myCmd.Parameters.Add("@Management", SqlDbType.VarChar).Value = MAN ;
                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }
    }


    [WebMethod]
    //private
    public string InsertFromPrivate(string fname, string lname, string email, string cemail, string phone)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_private_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
               

                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }


    // Think beyond
    [WebMethod]
    public string InsertFromThinkBeyond(string fname, string lname, string add, string city, string state, string zip, string email, string cemail, string phone)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_thinkbeyond_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@address", SqlDbType.VarChar).Value = add;
                myCmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                myCmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state;
                myCmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zip;

                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }


    [WebMethod]
    //Transfer
    public string InsertFromTransfer(string fname, string lname, string email, string cemail, string phone, bool day,bool night )
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_transfer_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@day", SqlDbType.VarChar).Value = day;
                myCmd.Parameters.Add("@night", SqlDbType.VarChar).Value = night;

                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }
    
    [WebMethod]
    // Transferyou Page
    public string InsertFromTransferYou(string fname, string lname, string add, string city, string state, string zip, string email, string cemail, string phone, bool day, bool night)
    {


        using (SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                Conn.Open();
                SqlCommand Cmd = new SqlCommand("CUS_sp_transferyou_INSERT", Conn);
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                Cmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                Cmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                Cmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                Cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = add;
                Cmd.Parameters.Add("@city", SqlDbType.VarChar).Value = city;
                Cmd.Parameters.Add("@state", SqlDbType.VarChar).Value = state;
                Cmd.Parameters.Add("@zip", SqlDbType.VarChar).Value = zip;
                Cmd.Parameters.Add("@day", SqlDbType.VarChar).Value = day;
                Cmd.Parameters.Add("@night", SqlDbType.VarChar).Value = night;
                Cmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                Conn.Close();
            }

        }



    }

    [WebMethod]
    //Welcome
    public string InsertFromWelcome(string fname, string lname, string email, string cemail, string phone)
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_welcome_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;


                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }

    [WebMethod]
    //Welcome Veterans
    public string InsertFromWelcomeVeterans(string fname, string lname, string email, string cemail, string phone,bool day,bool night )
    {


        using (SqlConnection myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();


                SqlCommand myCmd = new SqlCommand("CUS_sp_welcomeveterans_INSERT", myConn);
                myCmd.CommandType = CommandType.StoredProcedure;
                myCmd.Parameters.Add("@first_name", SqlDbType.VarChar).Value = fname;
                myCmd.Parameters.Add("@last_name", SqlDbType.VarChar).Value = lname;
                myCmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                myCmd.Parameters.Add("@confirmemail", SqlDbType.VarChar).Value = cemail;
                myCmd.Parameters.Add("@phone", SqlDbType.VarChar).Value = phone;
                myCmd.Parameters.Add("@day", SqlDbType.VarChar).Value = day;
                myCmd.Parameters.Add("@night", SqlDbType.VarChar).Value = night;


                myCmd.ExecuteNonQuery();
                string res = "Updated Compaign Tracking Table";
                return res;

            }

            catch (SqlException ex)
            {

                string errMsg = "Sql Exception Caught " + ex.Message.ToString();
                return errMsg;

            }
            finally
            {

                myConn.Close();
            }

        }



    }

     [WebMethod]
    //SiteStatistics 
    public string Addlog(string IP, string page, string pageRef, string browser, string OS, string fname, string lname, string email)
     {
        
         using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
         {
             try
             {
                 conn.Open();
                 SqlCommand Cmd = new SqlCommand("CUS_sp_Visitor_FromCatholic_INSERT", conn);
                 Cmd.CommandType = CommandType.StoredProcedure;

                 
                 Cmd.Parameters.Add("@vIP", SqlDbType.VarChar).Value = IP.ToString();
                 Cmd.Parameters.Add("@vpage", SqlDbType.VarChar).Value = page;
                 Cmd.Parameters.Add("@vref", SqlDbType.VarChar).Value = pageRef;
                 Cmd.Parameters.Add("@vbrowser", SqlDbType.VarChar).Value = browser;
                 Cmd.Parameters.Add("@vos", SqlDbType.VarChar).Value = OS;
                 Cmd.Parameters.Add("@fname", SqlDbType.VarChar).Value = fname;
                 Cmd.Parameters.Add("@lname", SqlDbType.VarChar).Value = lname;
                 Cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email;
                 Cmd.ExecuteNonQuery();
                 return "success";
             }
             catch (Exception e)
             {
                 string errMsg;
                 errMsg = e.Message.ToString();
                 return errMsg;
                 //System.Web.HttpContext.Current.Response.Write(err.Message);
             }
             finally
             {
                 conn.Close();
             }
         }


     }

}
